USE [ReportServer]
GO

DROP TRIGGER trig1
GO

DROP TRIGGER trig2
GO

DROP TABLE Subscriptions_history
GO

CREATE TABLE [dbo].[Subscriptions_history](
	[SubscriptionID] [uniqueidentifier] NOT NULL,
	[ExtensionSettings] [ntext] NULL,
	CONSTRAINT [PK_Subscriptions_history] PRIMARY KEY CLUSTERED([SubscriptionID])
	)

GO

CREATE TRIGGER trig1 
ON dbo.Event
INSTEAD OF INSERT
AS
DECLARE @SubscriptionID uniqueidentifier,
		@Settings varchar(max),
		@NewSettings varchar(max),
		@OldMail varchar(max),
		@NewMail varchar(max),
		@Subject varchar(max),
		@IndexFirstOf int,
		@IndexLastOf int
		
SET @NewMail = 'mail@example.com'

SELECT @SubscriptionID = EventData FROM inserted
SELECT @Settings = ExtensionSettings FROM Subscriptions WHERE SubscriptionID = @SubscriptionID

IF CHARINDEX('<name>To</name><value>',@Settings) != 0
BEGIN
SET @IndexFirstOf = CHARINDEX('<name>To</name><value>',@Settings) + 22
SET @IndexLastOf = CHARINDEX('</value>',@Settings,@IndexFirstOf)
	
SET @OldMail = SUBSTRING(@Settings,@IndexFirstOf,@IndexLastOf - @IndexFirstOf)
	
SET @IndexFirstOf = CHARINDEX('<name>Subject</name><value>',@Settings) + 27
SET @IndexLastOf = CHARINDEX('</value>',@Settings,@IndexFirstOf)
	
SET @Subject = SUBSTRING(@Settings,@IndexFirstOf,@IndexLastOf - @IndexFirstOf)
		
IF EXISTS(SELECT SubscriptionID FROM Subscriptions_history WHERE SubscriptionID = @SubscriptionID)

UPDATE Subscriptions_history
SET ExtensionSettings = CONVERT(ntext,@Settings)
WHERE SubscriptionID = @SubscriptionID

ELSE

INSERT INTO Subscriptions_history
SELECT SubscriptionID, ExtensionSettings FROM Subscriptions
WHERE SubscriptionID = @SubscriptionID

BEGIN TRANSACTION
UPDATE Subscriptions
SET ExtensionSettings = CONVERT(ntext,REPLACE(CONVERT(varchar(max),ExtensionSettings),@OldMail,@NewMail))
WHERE SubscriptionID = @SubscriptionID
	
UPDATE Subscriptions
SET ExtensionSettings = CONVERT(ntext,REPLACE(CONVERT(varchar(max),ExtensionSettings),@Subject,@OldMail))
WHERE SubscriptionID = @SubscriptionID
COMMIT TRANSACTION
END

INSERT INTO dbo.Event
SELECT * FROM inserted

GO

CREATE TRIGGER trig2
ON dbo.Event
AFTER DELETE
AS
DECLARE @SubscriptionID uniqueidentifier,
		@Settings varchar(max)

SELECT @SubscriptionID = EventData FROM deleted
SELECT @Settings = ExtensionSettings FROM Subscriptions_history WHERE SubscriptionID = @SubscriptionID

IF @Settings IS NOT NULL
UPDATE Subscriptions
SET ExtensionSettings = CONVERT(ntext,@Settings)
WHERE SubscriptionID = @SubscriptionID